package com.edu.app.mapper.ai;

import com.baomidou.mybatisplus.annotation.SqlParser;
import com.edu.app.bean.core.SysCustomer;
import com.edu.app.bean.core.SysUser;
import com.edu.app.bean.virtualbean.KeyValueItems;
import com.edu.app.bean.virtualbean.NameValueItems;
import com.edu.app.bean.virtualbean.UserActByDate;
import com.edu.app.bean.virtualbean.customize.PoCustomerStatus;
import com.edu.app.bean.virtualbean.customize.PoStudentStatus;
import com.edu.app.util.AppBaseMapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

@Component
public interface SysAIMapper extends AppBaseMapper<SysUser> {
    //'not in' is not supported in sql92 for jsqlparser, we have to exclude this sql and do
    //get tenant id manually
    @SqlParser(filter=true)
    @Select("select distinct t4.* from s_customer t4 " +
            "inner join s_user_customer t7 on t4.id = t7.customer_id " +
            "inner join s_user t8 on t8.id = t7.user_id " +
            "where t4.tenant_id = #{tenant_id} and t8.username = #{username} and t4.status='1' and t4.name not in " +
            "(select distinct(t3.name) from s_activity t1 " +
            "inner join s_customer_activity t2 on t1.id = t2.activity_id " +
            "inner join s_customer t3 on t3.id = t2.customer_id " +
            "inner join s_user_customer t5 on t5.customer_id = t3.id " +
            "inner join s_user t6 on t5.user_id = t6.id " +
            "where t6.username = #{username} and t1.time > DATE_SUB(now(), INTERVAL #{range} DAY))")
    List<SysCustomer> getNotActiveCustomer(@Param("username") String username, @Param("range") Integer range, @Param("tenant_id") Integer tenant_id);

    /*@Select("select id,name from (" +
            "select count(*) as cnt,id,name from " +
            "(" +
            "(select distinct t3.* from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t2.customer_id = t3.id " +
            "inner join s_customer_activity t4 on t3.id = t4.customer_id " +
            "inner join s_activity t5 on t5.id = t4.activity_id " +
            "where t1.username = #{username} and t3.status = '1') " +
            "union all " +
            "(select distinct t3.* from s_user t1 " +
            "inner join s_user_customer t2 on t1.id = t2.user_id " +
            "inner join s_customer t3 on t2.customer_id = t3.id " +
            "inner join s_customer_activity t4 on t3.id = t4.customer_id " +
            "inner join s_activity t5 on t5.id = t4.activity_id " +
            "where t1.username = #{username} and t5.status = '2' and t5.time > DATE_SUB(now(), INTERVAL #{range} DAY) and t5.time < now()) " +
            ")tb group by id,name" +
            ")tb2 where cnt=1")
    List<SysCustomer> getNotActiveCustomer(@Param("username") String username, @Param("range") Integer range);*/

    @Select("SELECT COUNT(*) FROM (SELECT t3.*,t4.type FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN S_LIST_OF_VALUE t4 on t4.id = t3.type_id " +
            "WHERE t1.username = #{username} and t4.type = 'S_LOV_LESSON' and t2.roleplay='teacher' and t3.time < Now() and t3.status = 0) AS tbl")
    Integer getTeacherPendingActivityNumber(String username);

    @Select("SELECT COUNT(*) FROM (SELECT t3.*,t4.type FROM S_USER t1 INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN S_LIST_OF_VALUE t4 on t4.id = t3.type_id " +
            "WHERE t1.username = #{username} and t4.type = 'S_LOV_LESSON' and t2.roleplay='teacher' and t3.time < Now() and t3.status = 1) AS tbl")
    Integer getTeacherPendingFeedbackNumber(String username);

    @SqlParser(filter=true)
    @Select("select tb3.rank id, tb3.username name,tb3.countNum value from (select tb1.username,tb1.countNum,@curRank := @curRank + 1 AS rank from " +
            "(SELECT t1.username,count(*) as countNum FROM S_USER t1 " +
            "INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "WHERE MONTH(t3.time) = MONTH(Now()) and t3.status = 2 GROUP BY t1.username ORDER BY countNum desc) tb1,(\n" +
            "SELECT @curRank := 0" +
            ") tb2) tb3 where tb3.username=#{username}")
    NameValueItems getTeacherLessonCompleteRank(@Param("username") String username, @Param("tenant_id") Integer tenant_id);

    @Select("select DISTINCT DATE_FORMAT(t1.time,'%Y-%m-%d %H:%i') as time,t1.id as activity_id, t1.title,t1.content,t3.id as user_id, " +
            "t3.nickname, group_concat(t7.name) as name, t8.value as activity_type from s_activity t1 " +
            "inner join s_user_activity t2 on t1.id = t2.activity_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "inner join s_user_role t4 on t4.user_id = t3.id " +
            "inner join s_role t5 on t4.role_id = t5.id " +
            "inner join s_customer_activity t6 on t1.id = t6.activity_id " +
            "inner join s_customer t7 on t6.customer_id = t7.id " +
            "inner join s_list_of_value t8 on t8.id = t1.type_id " +
            "where t1.status in (0,1,2) and Date(time) = #{date} and t5.role_name = #{role} group by time,activity_id,title,content,user_id,nickname order by time")
    List<UserActByDate> getUserActListByDateWithRole(@Param("date") String date, @Param("role") String role);

    @Select("select DISTINCT DATE_FORMAT(t1.time,'%H:%i') as time,t1.id as activity_id, t1.title,t1.content,t3.id as user_id, " +
            "t3.nickname, group_concat(t7.name) as name, t8.value as activity_type,t1.status from s_activity t1 " +
            "inner join s_user_activity t2 on t1.id = t2.activity_id " +
            "inner join s_user t3 on t3.id = t2.user_id " +
            "inner join s_user_role t4 on t4.user_id = t3.id " +
            "inner join s_role t5 on t4.role_id = t5.id " +
            "inner join s_customer_activity t6 on t1.id = t6.activity_id " +
            "inner join s_customer t7 on t6.customer_id = t7.id " +
            "inner join s_list_of_value t8 on t8.id = t1.type_id " +
            "where t3.username=#{username} and t8.type like '%${actType}%' and t7.status in (0,1) and t2.roleplay = #{role} and Date(time) = #{date} and t5.role_name = #{role} " +
            "group by status,time,activity_id,title,content,user_id,nickname order by time")
    List<UserActByDate> getUserActListByDateWithRoleAndUser(@Param("date") String date, @Param("role") String role, @Param("username") String username, @Param("actType") String actType);

    @Select("select id, nickname as name, count(count_num) as value from " +
            "(select t1.id as id, t1.nickname as nickname, t1.username as username, count(t5.name) as count_num " +
            "FROM S_USER t1 " +
            "INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN s_customer_activity t4 on t3.id = t4.activity_id " +
            "INNER JOIN s_customer t5 on t5.id = t4.customer_id " +
            "INNER JOIN s_list_of_value t6 on t6.id = t3.type_id " +
            "WHERE DATE(t3.time) = DATE(#{date}) and t3.status = 2 and t6.type = 'S_LOV_LESSON' " +
            "group by t1.username, t4.activity_id order by t4.activity_id) as tb1 where count_num=#{number} group by id")
    List<NameValueItems> getTeacherCompleteLessonNumberByDay(@Param("number") Integer number, @Param("date") String date);

    @Select("select id, nickname as name, count(count_num) as value from " +
            "(select t1.id as id, t1.nickname as nickname, t1.username as username, count(t5.name) as count_num " +
            "FROM S_USER t1 " +
            "INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN s_customer_activity t4 on t3.id = t4.activity_id " +
            "INNER JOIN s_customer t5 on t5.id = t4.customer_id " +
            "INNER JOIN s_list_of_value t6 on t6.id = t3.type_id " +
            "WHERE YEAR(t3.time) = YEAR(#{date}) and WEEK(t3.time,1) = WEEK(#{date},1) and t3.status = 2 and t6.type = 'S_LOV_LESSON' " +
            "group by t1.username, t4.activity_id order by t4.activity_id) as tb1 where count_num=#{number} group by id")
    //WEEK(t3.time,1)，后面这个1是mode，说明每周第一天从周一开始计算
    List<NameValueItems> getTeacherCompleteLessonNumberByWeek(@Param("number") Integer number, @Param("date") String date);

    @Select("select id, nickname as name, count(count_num) as value from " +
            "(select t1.id as id, t1.nickname as nickname, t1.username as username, count(t5.name) as count_num " +
            "FROM S_USER t1 " +
            "INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN s_customer_activity t4 on t3.id = t4.activity_id " +
            "INNER JOIN s_customer t5 on t5.id = t4.customer_id " +
            "INNER JOIN s_list_of_value t6 on t6.id = t3.type_id " +
            "WHERE YEAR(t3.time) = YEAR(#{date}) and MONTH(t3.time) = MONTH(#{date}) and t3.status = 2 and t6.type = 'S_LOV_LESSON' " +
            "group by t1.username, t4.activity_id order by t4.activity_id) as tb1 where count_num=#{number} group by id")
    List<NameValueItems> getTeacherCompleteLessonNumberByMonth(@Param("number") Integer number, @Param("date") String date);

    @Select("select id, nickname as name, count(count_num) as value from " +
            "(select t1.id as id, t1.nickname as nickname, t1.username as username, count(t5.name) as count_num " +
            "FROM S_USER t1 " +
            "INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN s_customer_activity t4 on t3.id = t4.activity_id " +
            "INNER JOIN s_customer t5 on t5.id = t4.customer_id " +
            "INNER JOIN s_list_of_value t6 on t6.id = t3.type_id " +
            "WHERE DATE(t3.time) = DATE(#{date}) and t3.status = 2 and t6.type = 'S_LOV_LESSON_DEMO' " +
            "group by t1.username, t4.activity_id order by t4.activity_id) as tb1 where count_num=#{number} group by id")
    List<NameValueItems> getTeacherDemoLessonNumberByDay(@Param("number") Integer number, @Param("date") String date);

    @Select("select id, nickname as name, count(count_num) as value from " +
            "(select t1.id as id, t1.nickname as nickname, t1.username as username, count(t5.name) as count_num " +
            "FROM S_USER t1 " +
            "INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN s_customer_activity t4 on t3.id = t4.activity_id " +
            "INNER JOIN s_customer t5 on t5.id = t4.customer_id " +
            "INNER JOIN s_list_of_value t6 on t6.id = t3.type_id " +
            "WHERE YEAR(t3.time) = YEAR(#{date}) and WEEK(t3.time,1) = WEEK(#{date},1) and t3.status = 2 and t6.type = 'S_LOV_LESSON_DEMO' " +
            "group by t1.username, t4.activity_id order by t4.activity_id) as tb1 where count_num=#{number} group by id")
        //WEEK(t3.time,1)，后面这个1是mode，说明每周第一天从周一开始计算
    List<NameValueItems> getTeacherDemoLessonNumberByWeek(@Param("number") Integer number, @Param("date") String date);

    @Select("select id, nickname as name, count(count_num) as value from " +
            "(select t1.id as id, t1.nickname as nickname, t1.username as username, count(t5.name) as count_num " +
            "FROM S_USER t1 " +
            "INNER JOIN S_USER_ACTIVITY t2 on t1.ID = t2.USER_ID " +
            "INNER JOIN S_ACTIVITY t3 on t2.ACTIVITY_ID = t3.ID " +
            "INNER JOIN s_customer_activity t4 on t3.id = t4.activity_id " +
            "INNER JOIN s_customer t5 on t5.id = t4.customer_id " +
            "INNER JOIN s_list_of_value t6 on t6.id = t3.type_id " +
            "WHERE YEAR(t3.time) = YEAR(#{date}) and MONTH(t3.time) = MONTH(#{date}) and t3.status = 2 and t6.type = 'S_LOV_LESSON_DEMO' " +
            "group by t1.username, t4.activity_id order by t4.activity_id) as tb1 where count_num=#{number} group by id")
    List<NameValueItems> getTeacherDemoLessonNumberByMonth(@Param("number") Integer number, @Param("date") String date);

    @Select("select distinct t1.id, t1.name, t3.time, t5.nickname as teacher from s_customer t1 " +
            "inner join s_customer_activity t2 on t1.id = t2.customer_id " +
            "inner join s_activity t3 on t2.activity_id = t3.id " +
            "inner join s_user_customer t4 on t4.customer_id = t1.id " +
            "inner join s_user t5 on t5.id = t4.user_id " +
            "inner join s_user_activity t6 on (t6.user_id = t5.id and t6.activity_id = t3.id) " +
            "where t1.status = '0' and DATE(t3.time) = DATE(Now()) order by t3.time desc")
    List<PoStudentStatus> getPoStudentStatusToday();

    @Select("select distinct t1.id, t1.name, t3.time, t5.nickname as teacher from s_customer t1 " +
            "inner join s_customer_activity t2 on t1.id = t2.customer_id " +
            "inner join s_activity t3 on t2.activity_id = t3.id " +
            "inner join s_user_customer t4 on t4.customer_id = t1.id " +
            "inner join s_user t5 on t5.id = t4.user_id " +
            "inner join s_user_activity t6 on (t6.user_id = t5.id and t6.activity_id = t3.id) " +
            "where t1.status = '0' and DATE(t3.time) > DATE(Now()) order by t3.time desc")
    List<PoStudentStatus> getPoStudentStatusFuture();

    @Select("select distinct t1.id, t1.name, t3.time, t5.nickname as teacher, t1.tag from s_customer t1 " +
            "inner join s_customer_activity t2 on t1.id = t2.customer_id " +
            "inner join s_activity t3 on t2.activity_id = t3.id " +
            "inner join s_user_customer t4 on t4.customer_id = t1.id " +
            "inner join s_user t5 on t5.id = t4.user_id " +
            "inner join s_user_activity t6 on (t6.user_id = t5.id and t6.activity_id = t3.id) " +
            "where t1.status = '0' and DATE(t3.time) < DATE(Now()) order by t3.time desc")
    List<PoStudentStatus> getPoStudentStatusPast();

    @Select("select distinct t1.id, t1.name, t3.time, t5.nickname as teacher, t1.tag from s_customer t1 " +
            "inner join s_customer_activity t2 on t1.id = t2.customer_id " +
            "inner join s_activity t3 on t2.activity_id = t3.id " +
            "inner join s_user_customer t4 on t4.customer_id = t1.id " +
            "inner join s_user t5 on t5.id = t4.user_id " +
            "inner join s_user_activity t6 on (t6.user_id = t5.id and t6.activity_id = t3.id) " +
            "where t1.status = '0' order by t3.time desc")
    List<PoStudentStatus> getPoStudentStatusAll();

    @Select("select distinct t1.id, t1.name, t3.time, t5.nickname as teacher, t1.tag from s_customer t1 " +
            "inner join s_customer_activity t2 on t1.id = t2.customer_id " +
            "inner join s_activity t3 on t2.activity_id = t3.id " +
            "inner join s_user_customer t4 on t4.customer_id = t1.id " +
            "inner join s_user t5 on t5.id = t4.user_id " +
            "inner join s_user_activity t6 on (t6.user_id = t5.id and t6.activity_id = t3.id) " +
            "where t1.status = '0' and t5.nickname=#{nickname} order by t3.time desc")
    List<PoStudentStatus> getPoStudentStatusMine(String nickname);

    @Select("select count(*) as value, DAY(t.create_time) as keycol from s_customer t where t.create_time like CONCAT(#{dateYYMM},'%') group by DAY(t.create_time)")
    @Results(id = "kvmap", value = {
            @Result(property = "value", column = "value"),
            @Result(property = "key", column = "keycol")
            }
            )
    List<KeyValueItems> getMarketingStatus(String dateYYMM);

    @Results(  {
            @Result(property = "value", column = "value"),
            @Result(property = "key", column = "keycol")
    }
    )
    @Select("select count(*) as value, DAY(time) as keycol from " +
            "(select distinct t1.*,DATE(t3.time) as time, t4.value from s_customer t1 inner join s_customer_activity t2 on t1.id = t2.customer_id " +
            "inner join s_activity t3 on t2.activity_id = t3.id " +
            "inner join s_list_of_value t4 on t4.id = t3.type_id " +
            "where t1.status in (0,8) and t4.type='S_LOV_LESSON_DEMO' and t3.time < now() and t3.time like CONCAT(#{dateYYMM},'%')) as tb1 group by DAY(time)")
    List<KeyValueItems> getDemoClassStatus(String dateYYMM);

    @Results(  {
            @Result(property = "value", column = "value"),
            @Result(property = "key", column = "keycol")
    }
    )
    @Select("select count(*) as value, DAY(t1.time) as keycol from s_activity t1 " +
            " inner join s_list_of_value t2 on t2.id = t1.type_id where t1.time like CONCAT(#{dateYYMM},'%') and t1.status='2' and t2.type='S_LOV_LESSON' group by DAY(t1.time)")
    List<KeyValueItems> getClassCompleteStatus(String dateYYMM);

    /*@Select("select name as 'keycol',round(value/total,2) as value from (select count(*) as value,TIMESTAMPDIFF(year,birthday,curdate()) as name from s_customer " +
            "where status='1' and birthday is not null and year(birthday)<year(curdate()) group by TIMESTAMPDIFF(year,birthday,curdate()) order by TIMESTAMPDIFF(year,birthday,curdate())) tb1, " +
            "(select count(*) as total from s_customer where status='1' and birthday is not null and year(birthday)<year(curdate())) tb2;")
    List<KeyValueItems> getMemberAgeRange();*/
    @Results(  {
            @Result(property = "value", column = "value"),
            @Result(property = "key", column = "keycol")
    }
    )

    @Select("select count(*) as value,TIMESTAMPDIFF(year,birthday,curdate()) as keycol " +
            "from s_customer where status='1' and birthday is not null and year(birthday)<year(curdate()) " +
            "group by TIMESTAMPDIFF(year,birthday,curdate()) order by TIMESTAMPDIFF(year,birthday,curdate())")
    List<KeyValueItems> getMemberAgeRange();

    @Select("select create_time,create_by,id,tag,group_concat(time separator ' | ' ) as time,name,birthday,description,nickname as consultant,group_concat(comments separator ' ') as comments,stage from " +
            "(select distinct t1.create_time,t8.nickname as create_by,t1.tag,t1.id,t5.time,t1.name,t1.birthday,t1.description,t3.nickname,concat_ws(' ',t6.create_time,t6.content) as comments,t7.value as stage from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "inner join s_customer_activity t4 on t1.id = t4.customer_id " +
            "inner join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "where t2.roleplay='consultant' and t1.status='0' and DATE(t5.time) <= DATE(Now()) order by time desc, comments desc) tb1 group by id,name,birthday,description,nickname,stage order by time desc")
    List<PoCustomerStatus> getPoCustomerStatusCompleteDemoClassAll();

    @Select("select create_time,create_by,id,tag,substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time,name,birthday,description,nickname as consultant,substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as comments,stage from " +
            "(select distinct t1.create_time,t8.nickname as create_by,t1.tag,t1.id,t5.time,t1.name,t1.birthday,t1.description,t3.nickname,concat_ws(' ',t6.create_time,t6.content) as comments,t7.value as stage from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "left join s_customer_activity t4 on t1.id = t4.customer_id " +
            "left join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "where t3.username=#{username} and (t7.value like #{stage} or t7.value is null) and t2.roleplay='consultant' and t1.status='0' order by time desc, comments desc) tb1 group by id,name,birthday,description,nickname,stage order by time desc")
    List<PoCustomerStatus> getPoCustomerNotStartByUser(@Param("username") String username, @Param("stage") String stage);

    @Select("select create_time,create_by,id,tag,substring_index(GROUP_CONCAT(time SEPARATOR '|'),'|',1) as time,name,DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(birthday)), '%Y')+0 AS age,description,nickname as consultant,substring_index(GROUP_CONCAT(comments SEPARATOR '|'),'|',1) as comments,stage from " +
            "(select distinct t1.create_time,t8.nickname as create_by,t1.tag,t1.id,t5.time,t1.name,t1.birthday,t1.description,t3.nickname,concat_ws(' ',t6.create_time,t6.content) as comments,t7.value as stage from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "left join s_customer_activity t4 on t1.id = t4.customer_id " +
            "left join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "where t3.username=#{username} and t7.value like #{stage} and t2.roleplay='consultant' and t1.status='0' order by time desc, comments desc) tb1 group by id,name,birthday,description,nickname,stage order by time desc")
    List<PoCustomerStatus> getPoCustomerByUser(@Param("username") String username, @Param("stage") String stage);

    @Select("select count(*) as number from " +
            "(select distinct t1.create_time,t8.nickname as create_by,t1.tag,t1.id,t5.time,t1.name,t1.birthday,t1.description,t3.nickname,concat_ws(' ',t6.create_time,t6.content) as comments,t7.value as stage from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "inner join s_customer_activity t4 on t1.id = t4.customer_id " +
            "inner join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "where t2.roleplay='consultant' and t1.status='0' and DATE(t5.time) <= DATE(Now()) order by time desc, comments desc) tb1")
    Integer getPoCustomerStatusCompleteDemoClassAllNumber();

    @Select("select distinct t1.create_time,t8.nickname as create_by,t1.id,t1.tag,group_concat(t5.time separator ' | ' ) as time," +
            "t1.name,t1.birthday,t1.description,group_concat(t3.nickname separator ',') as consultant," +
            "group_concat(concat_ws(' ',t6.create_time,t6.content) separator ' ') as comments, t7.value as stage " +
            "from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "left join s_customer_activity t4 on t1.id = t4.customer_id " +
            "left join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "where t1.status='0' and t2.roleplay='consultant' and t1.id not in (select distinct t1.id from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_customer_activity t4 on t1.id = t4.customer_id " +
            "inner join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "where t2.roleplay='consultant' and t1.status='0' and DATE(t5.time) <= DATE(Now())) group by id,tag,name,birthday,description order by t1.create_time desc")
    List<PoCustomerStatus> getPoCustomerStatusPendingDemoClassAll();

    @Select("select count(*) as number from (select distinct t1.create_time,t8.nickname as create_by,t1.id,t1.tag,group_concat(t5.time separator ' | ' ) as time," +
            "t1.name,t1.birthday,t1.description,group_concat(t3.nickname separator ',') as consultant," +
            "group_concat(concat_ws(' ',t6.create_time,t6.content) separator ' ') as comments, t7.value as stage " +
            "from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_user t8 on t8.username = t1.create_by " +
            "left join s_customer_activity t4 on t1.id = t4.customer_id " +
            "left join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "where t1.status='0' and t2.roleplay='consultant' and t1.id not in (select distinct t1.id from s_customer t1 " +
            "inner join s_user_customer t2 on t1.id = t2.customer_id " +
            "inner join s_user t3 on t2.user_id = t3.id " +
            "inner join s_customer_activity t4 on t1.id = t4.customer_id " +
            "inner join s_activity t5 on t4.activity_id = t5.id " +
            "left join s_customer_trace t6 on t1.id = t6.customer_id " +
            "left join s_list_of_value t7 on t1.stage_id = t7.id " +
            "where t2.roleplay='consultant' and t1.status='0' and DATE(t5.time) <= DATE(Now())) group by id,tag,name,birthday,description order by t1.create_time desc) as tb1")
    Integer getPoCustomerStatusPendingDemoClassAllNumber();


    /*New APIs for both sales and teacher*/
    @Select("select id, nickname as name, count(*) as value from " +
            "(select distinct t1.id,t1.nickname,t3.time,t5.name from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_customer_activity t4 on t3.id = t4.activity_id " +
            "inner join s_customer t5 on t5.id = t4.customer_id " +
            "inner join s_list_of_value t6 on t6.id = t3.type_id " +
            "where YEAR(t3.time) = YEAR(#{date}) and MONTH(t3.time) = MONTH(#{date}) and t3.status = #{status} and t6.type = #{type}) tb1 " +
            "group by id,nickname order by value desc")
    List<NameValueItems> getUserActivityTotalNumber(@Param("status") Integer status, @Param("type") String type, @Param("date") String date);

    @Select("select id, nickname as name, count(*) as value from " +
            "(select distinct t1.id,t1.nickname,t3.time from s_user t1 " +
            "inner join s_user_activity t2 on t1.id = t2.user_id " +
            "inner join s_activity t3 on t3.id = t2.activity_id " +
            "inner join s_list_of_value t6 on t6.id = t3.type_id " +
            "where YEAR(t3.time) = YEAR(#{date}) and MONTH(t3.time) = MONTH(#{date}) and t3.status = #{status} and t6.type = #{type}) tb " +
            "group by id,nickname order by value desc")
    List<NameValueItems> getUserActivityNumber(@Param("status") Integer status, @Param("type") String type, @Param("date") String date);
}